#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
检查数据库中实际存储的时间数据，对比record_time和created_at_ms
"""

import pymysql
import configparser
import os
from datetime import datetime
import pytz

def main():
    # 加载配置
    config = configparser.ConfigParser()
    config_path = 'config.ini'
    config.read(config_path, encoding='utf-8')

    db_config = dict(config['database'])
    for key, value in db_config.items():
        db_config[key] = value.strip('"\'').rstrip(',')

    try:
        # 连接数据库
        connection = pymysql.connect(
            host=db_config['host'],
            port=int(db_config['port']),
            user=db_config['user'],
            password=db_config['password'],
            db=db_config['db'],
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )

        with connection.cursor() as cursor:
            # 查询最新的时间数据
            sql = """
            SELECT record_time, created_at_ms 
            FROM device_data_summary 
            WHERE record_time IS NOT NULL AND created_at_ms IS NOT NULL
            ORDER BY record_time DESC 
            LIMIT 5
            """
            cursor.execute(sql)
            results = cursor.fetchall()
            
            if not results:
                print("数据库中没有找到时间数据！")
                return
            
            print("=== 数据库时间数据对比 ===")
            print(f"{'record_time':<20} | {'created_at_ms':<15} | {'转换后的荷兰时间':<20} | {'差异(小时)'}")
            print("-" * 80)
            
            amsterdam_tz = pytz.timezone('Europe/Amsterdam')
            
            for row in results:
                record_time_str = row['record_time']
                created_at_ms = row['created_at_ms']
                
                # 将毫秒时间戳转换为荷兰时间
                timestamp_seconds = created_at_ms / 1000
                utc_datetime = datetime.fromtimestamp(timestamp_seconds, tz=pytz.utc)
                netherlands_datetime = utc_datetime.astimezone(amsterdam_tz)
                
                # 将record_time转换为datetime（处理可能的datetime对象）
                if isinstance(record_time_str, datetime):
                    record_datetime = record_time_str
                else:
                    record_datetime = datetime.strptime(str(record_time_str), '%Y-%m-%d %H:%M:%S')
                # 假设record_time是荷兰本地时间
                if record_datetime.tzinfo is None:
                    record_datetime = amsterdam_tz.localize(record_datetime)
                
                # 计算差异
                diff_seconds = abs((netherlands_datetime - record_datetime).total_seconds())
                diff_hours = diff_seconds / 3600
                
                print(f"{record_time_str:<20} | {created_at_ms:<15} | {netherlands_datetime.strftime('%Y-%m-%d %H:%M:%S'):<20} | {diff_hours:.1f}")
            
            # 获取当前正确的荷兰时间进行对比
            print(f"\n=== 当前时间对比 ===")
            current_local = datetime.now()
            current_utc = datetime.utcnow()
            current_netherlands = current_utc.replace(tzinfo=pytz.utc).astimezone(amsterdam_tz)
            
            print(f"当前本地时间: {current_local.strftime('%Y-%m-%d %H:%M:%S')}")
            print(f"当前UTC时间: {current_utc.strftime('%Y-%m-%d %H:%M:%S')}")
            print(f"当前荷兰时间: {current_netherlands.strftime('%Y-%m-%d %H:%M:%S')}")
            
            # 检查是否是冬令时
            is_dst = current_netherlands.dst().total_seconds() > 0
            print(f"当前荷兰是否夏令时: {is_dst}")
            print(f"荷兰时区: {current_netherlands.tzname()}")
            
        connection.close()
        
    except Exception as e:
        print(f"数据库操作失败: {e}")

if __name__ == "__main__":
    main()